﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Marmat.Forms.Design
{
    public class DefalutDataGridService : IDataGridService
    {

        const string getTableCollectionString = @"declare @version int
set @version = CAST(SUBSTRING(@@version,22,4) as int)

IF (@version = 2000)
BEGIN
	SELECT     TOP 100 PERCENT tabs.name, tabs.xtype, dc.MS_Description
	FROM         dbo.sysobjects AS tabs LEFT OUTER JOIN
							  (SELECT     id, value AS MS_Description
								FROM          dbo.sysproperties
								WHERE      (name = N'MS_Description') AND (type = 3)) AS dc ON tabs.id = dc.id
	WHERE     (tabs.xtype IN ('U','V'))
	ORDER BY tabs.xtype
END
ELSE
IF (@version > 2000)
BEGIN
	SELECT	tabs.name, tabs.xtype, dc.MS_Description
	FROM	sys.sysobjects AS tabs LEFT OUTER JOIN
							  (SELECT     major_id, value AS MS_Description
								FROM          sys.extended_properties
								WHERE      (name = N'MS_Description') AND (minor_id = 0)) AS dc ON tabs.id = dc.major_id
	WHERE     (tabs.xtype IN ('U','V'))
	ORDER BY tabs.xtype
END";
        const string getColumnCollectionString = @"declare @version int
declare @tableName nvarchar(100) = N'{0}';
set @version = CAST(SUBSTRING(@@version,22,4) as int)
IF (@version = 2000)
BEGIN
	select
		 c.name as [Name]
		 ,convert(bit,case when exists(select 1 from sysobjects where xtype='PK' and parent_obj=c.id and name in (
			 select name from sysindexes where indid in(
				 select indid from sysindexkeys where id = c.id and colid=c.colid))) then 1 else 0 end) 
					 as [PrimaryKey]
		 ,convert(bit,c.IsNullable)  as [IsNullable]
		 ,t.name AS TypeName
		 ,convert(bit,COLUMNPROPERTY(c.id,c.name,'IsIdentity')) as [IsIdentity]
		 ,c.Length as [占用字节] 
		 ,COLUMNPROPERTY(c.id,c.name,'PRECISION') as [MaxLength]
		 ,isnull(COLUMNPROPERTY(c.id,c.name,'Scale'),0) as [小数位数]
		 ,ISNULL(CM.text,'') as [默认值]
		 ,isnull(ETP.value,'') AS [Description]
	from syscolumns c
	inner join systypes t on c.xusertype = t.xusertype 
	left join sysproperties ETP on ETP.id = c.id and ETP.smallid = c.colid and ETP.name ='MS_Description' 
	left join syscomments CM on c.cdefault = CM.id
	where c.id = object_id(@tableName)
END
ELSE
IF (@version > 2000 )
BEGIN
	select
		 c.name as [Name]
		 ,convert(bit,case when exists(select 1 from sysobjects where xtype='PK' and parent_obj=c.id and name in (
			 select name from sysindexes where indid in(
				 select indid from sysindexkeys where id = c.id and colid=c.colid))) then 1 else 0 end) 
					 as [PrimaryKey]
		 ,convert(bit,c.IsNullable)  as [IsNullable]
		 ,t.name AS TypeName
		 ,convert(bit,COLUMNPROPERTY(c.id,c.name,'IsIdentity')) as [IsIdentity]
		 ,c.Length as [占用字节] 
		 ,COLUMNPROPERTY(c.id,c.name,'PRECISION') as [MaxLength]
		 ,isnull(COLUMNPROPERTY(c.id,c.name,'Scale'),0) as [小数位数]
		 ,ISNULL(CM.text,'') as [默认值]
		 ,isnull(ETP.value,'') AS [Description]
	from syscolumns c
	inner join systypes t on c.xusertype = t.xusertype 
	left join sys.extended_properties ETP on ETP.major_id = c.id and ETP.minor_id = c.colid and ETP.name ='MS_Description' 
	left join syscomments CM on c.cdefault=CM.id
	where c.id = object_id(@tableName)
END";


        ConnectionDescription[] IDataGridService.GetConnectionCollection()
        {
            List<ConnectionDescription> collection = new List<ConnectionDescription>();
            foreach (var item in Marmat.Data.Connections.Items)
            {
                ConnectionDescription connection = new ConnectionDescription();
                connection.Name = item.Key;
                collection.Add(connection);
            }
            return collection.ToArray();
        }

        TableDescription[] IDataGridService.GetTableCollection(string connectionName)
        {

            List<TableDescription> collection = new List<TableDescription>();
            Marmat.Data.SQLCommand command = new Marmat.Data.SQLCommand();
            command.ConnectionName = connectionName;
            command.CommandText = getTableCollectionString;
            System.Data.DataTable data = command.ExecuteReader();

            foreach (System.Data.DataRow row in data.Rows)
            {
                TableDescription table = new TableDescription();
                table.Name = row["name"].ToString();
                table.Description = row["MS_Description"].ToString();
                table.Group = row["xtype"].ToString().Trim() == "U" ? "Tables" : "Views";
                collection.Add(table);
            }
            return collection.ToArray();
        }

        ColumnDescription[] IDataGridService.GetColumnDescriptionCollection(string connectionName, string modelName)
        {
            List<ColumnDescription> collection = new List<ColumnDescription>();
            Marmat.Data.SQLCommand command = new Marmat.Data.SQLCommand();
            command.ConnectionName = connectionName;
            command.CommandText = string.Format(getColumnCollectionString, modelName);
            System.Data.DataTable data = command.ExecuteReader();

            foreach (System.Data.DataRow row in data.Rows)
            {
                ColumnDescription column = new ColumnDescription();
                column.Name = row["Name"].ToString();
                column.Caption = row["Description"].ToString();
                column.MaxLength = Convert.ToInt32(row["MaxLength"]);
                column.IsPrimaryKey = (bool)row["PrimaryKey"];
                column.IsRequired = !(bool)row["IsNullable"];
                column.IsIdentity = Convert.ToBoolean(row["IsIdentity"]);
                switch (row["TypeName"].ToString())
                {
                    case "char":
                    case "nchar":
                    case "varchar":
                    case "nvarchar":
                    case "text":
                    case "ntext":
                        column.DataType = typeof(string);
                        break;
                    case "bit":
                        column.DataType = typeof(bool);
                        break;
                    case "int":
                        column.DataType = typeof(int);
                        break;
                    case "real":
                        column.DataType = typeof(float);
                        break;
                    case "float":
                    case "money":
                        column.DataType = typeof(double);
                        break;
                    case "numeric":
                    case "decimal":
                        column.DataType = typeof(decimal);
                        break;
                    case "date":
                    case "datetime":
                        column.DataType = typeof(DateTime);
                        break;
                    default:
                        break;
                }
                collection.Add(column);
            }
            return collection.ToArray();
        }


    }
}
